
if exists (select 1 from sysobjects where name = 'get_personeninabteilunginschema' and type = 'P')
begin
   drop procedure get_personeninabteilunginschema
   print 'Procedure: get_personeninabteilunginschema deleted ...'
end
go
create procedure get_personeninabteilunginschema(
  @abteilungid       int           = 1,
  @schemaid          int           = 25
)
as
begin
  set nocount on   
   
  select prsid = p.PrsId,
         anzeigename = p.Vorname + ' ' + p.Nachname,
         vorname = p.Vorname,
         nachname = p.Nachname
    from Schemas s
   inner join PlanSchema ps
      on ps.SchemaID = s.SchemaID
   inner join Abteilung a
      on a.AbteilungID = ps.AbteilungID
   inner join Person p
      on p.PrsId = ps.PrsID
   where s.SchemaID = @schemaid
     and ps.AbteilungID = @abteilungid
     and ps.PrsID <> 0
   order by PrsSchemaID

end
go
print 'Procedure: get_personeninabteilunginschema done ...'
go
grant exec on get_personeninabteilunginschema to prsadmins with grant option
go
grant exec on get_personeninabteilunginschema to prsusers
go

